﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.IO;
using System.Collections;

namespace jobsheetFinal
{
    class DBManager
    {
        static string CONNECTIONSTRING = "Server=localhost;database=JobSheetDB;Trusted_Connection=true";




        /*---------------- insert the new user -----------------------*/
        public static void RegisterNewUser_Admin(User u)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                SqlCommand com = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                com.Connection = conn;
                string query = "INSERT INTO Group_Admin(firstName,lastName,jobTitle,emailAddress,userName,the_password)";
                query +=       "VALUES              (@firstName,@lastName,@jobTitle,@emailAddress,@userName,@the_password)";
                com.CommandText = query;

                com.Parameters.AddWithValue("@firstName", u.FirstName);
                com.Parameters.AddWithValue("@lastName", u.LastName);
                com.Parameters.AddWithValue("@jobTitle", u.JobTitle);
                com.Parameters.AddWithValue("@emailAddress", u.EmailAddress);
                com.Parameters.AddWithValue("@userName", u.UserName);
                com.Parameters.AddWithValue("@the_password", u.Password);

                int rowsAdded = (int)com.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
                   
        }

        public static void RegisterNewUser_Sales(User u)
        {
            SqlConnection conn = new SqlConnection();

            try
            {
                SqlCommand com = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                com.Connection = conn;
                string query = "INSERT INTO Group_Sales(firstName,lastName,jobTitle,emailAddress,userName,the_password)";
                query += "VALUES              (@firstName,@lastName,@jobTitle,@emailAddress,@userName,@the_password)";
                com.CommandText = query;

                com.Parameters.AddWithValue("@firstName", u.FirstName);
                com.Parameters.AddWithValue("@lastName", u.LastName);
                com.Parameters.AddWithValue("@jobTitle", u.JobTitle);
                com.Parameters.AddWithValue("@emailAddress", u.EmailAddress);
                com.Parameters.AddWithValue("@userName", u.UserName);
                com.Parameters.AddWithValue("@the_password", u.Password);

                int rowsAdded = (int)com.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }

        }

        public static bool LoginValidateRegisterAdmin(string userName, string password)
        {
            bool result = false;
            SqlConnection con = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                con.ConnectionString = CONNECTIONSTRING;
                con.Open();
                comm.Connection = con;
                comm.CommandText = "SELECT userName, the_password from Group_Admin WHERE userName=@userName and the_password=@the_password";
                comm.Parameters.AddWithValue("@userName", userName);
                comm.Parameters.AddWithValue("@the_password", password);

                SqlDataReader dr = comm.ExecuteReader();
                result = dr.Read();
            }
            catch (Exception ex)
            {
              
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public static bool LoginValidateRegisterSales(string userName, string password)
        {
            bool result = false;
            SqlConnection con = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                con.ConnectionString = CONNECTIONSTRING;
                con.Open();
                comm.Connection = con;
                comm.CommandText = "SELECT userName, the_password from Group_Sales WHERE userName=@userName and the_password=@the_password";
                comm.Parameters.AddWithValue("@userName", userName);
                comm.Parameters.AddWithValue("@the_password", password);

                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public static void ServiceRequest(ServiceRequest jobSheet)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = CONNECTIONSTRING;
            try
            {
                con.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = con;

                string query = "INSERT INTO ECS_JobSheet(jobSheetRequest,preferredServiceDate,jobSheetNumber,SONumber,ECS_PO_Num,project,reseller,re_address,re_contactPerson, ";
                query += "re_mobile,re_email,enduser,end_address,end_contactPerson,end_Mobile,end_Email,serviceCode,descriptions,quantity,price,";
                query += "remark)";

                query += "VALUES (@jobSheetRequest,@preferredServiceDate,@jobSheetNumber,@SONumber,@ECS_PO_Num,@project,@reseller,@re_address,@re_contactPerson,";
                query += "@re_mobile,@re_email,@enduser,@end_address,@end_contactPerson,@end_Mobile,@end_Email,@serviceCode,@descriptions,@quantity,@price,";
                query += "@remark);";

                comm.CommandText = query;

                comm.Parameters.AddWithValue("@jobSheetRequest", jobSheet.jobSheetNum);
                comm.Parameters.AddWithValue("@preferredServiceDate", jobSheet.preferredServiceDate);
                comm.Parameters.AddWithValue("@jobSheetNumber", jobSheet.jobSheetNum);
                comm.Parameters.AddWithValue("@SONumber", jobSheet.SO_Number);
                comm.Parameters.AddWithValue("@ECS_PO_Num", jobSheet.ECS_PO_Num);
                comm.Parameters.AddWithValue("@project", jobSheet.project);
                comm.Parameters.AddWithValue("@reseller", jobSheet.reseller);
                comm.Parameters.AddWithValue("@re_address", jobSheet.re_address);
                comm.Parameters.AddWithValue("@re_contactPerson", jobSheet.re_contactPerson);
                comm.Parameters.AddWithValue("@re_mobile", jobSheet.re_mobile);
                comm.Parameters.AddWithValue("@re_email", jobSheet.re_email);
                comm.Parameters.AddWithValue("@enduser", jobSheet.endUser);
                comm.Parameters.AddWithValue("@end_address", jobSheet.end_address);
                comm.Parameters.AddWithValue("@end_contactPerson", jobSheet.end_contactPerson);
                comm.Parameters.AddWithValue("@end_Email", jobSheet.end_emil);
                comm.Parameters.AddWithValue("@end_mobile", jobSheet.end_mobileNumber);
                comm.Parameters.AddWithValue("@serviceCode", jobSheet.serviceCode);
                comm.Parameters.AddWithValue("@descriptions", jobSheet.description);
                comm.Parameters.AddWithValue("@quantity", jobSheet.quantity);
                comm.Parameters.AddWithValue("@price", jobSheet.price);
                comm.Parameters.AddWithValue("@remark", jobSheet.remark);

                int rowsAdded = (int)comm.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public static ArrayList GetJobsheets()
        {
            ArrayList result = new ArrayList();                  
               SqlConnection con = new SqlConnection();
           
           try
           {
               // step 1: Connect to the databasecon.Open();
               con.ConnectionString = CONNECTIONSTRING;
               con.Open();

               // step 2: Prepare the SQL command
               SqlCommand comm = new SqlCommand();
               comm.Connection = con;
            
               



           }
           catch (Exception ex)
           {
               throw new Exception(ex.Message);
           }
           finally
           {
               con.Close();
           }
           return result;


            
        }

    }


}